﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetPrePostInfo_V3]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetPrePostInfo_V3];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetPrePostInfo_V3]
    @StaffName      nvarchar(300),
    @DocID          int,
    @NextFlowRule   INT = -2 OUTPUT,
    @NextFlowID     INT = -2 OUTPUT,
    @NextStepID     INT = -2 OUTPUT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Flow_ID int
DECLARE @Step_ID int
DECLARE @StaffID INT
DECLARE @StaffCaste INT

-- get the id of the specified staff
SELECT @StaffID = Staff_id, @StaffCaste = Caste FROM dbo.UDS_Staff WHERE Staff_Name = @StaffName;
IF @@rowcount <> 1
BEGIN
    raiserror(N'The specified user "%s" cannot be found.', 16, 1, @StaffName);
    return -1;
END;

-- 得到文档的当前流程ID和步骤ID
SELECT @Flow_ID = Flow_ID, @Step_ID = Step_ID FROM dbo.uds_flow_document WHERE Doc_ID = @DocID ;
IF @@ROWCOUNT = 0
BEGIN
    raiserror(N'The specified document "%d" cannot be found.', 16, 1, @DocID);
    return -1;
END

--该步骤是否存在
IF EXISTS(SELECT 1 FROM dbo.UDS_Flow_Step WHERE Flow_id = @flow_id AND Step_id = @step_id)
BEGIN
        
    --根据条件判断，得到流转到的ID    
    DECLARE @FieldName  nvarchar(10)
    DECLARE @Compare    nvarchar(10)
    DECLARE @Compare_Value    INT
    DECLARE @To_Step_id       INT
    DECLARE @To_Flow_Id       INT
    DECLARE @Jump_Flow_Rule   INT
    
    SET @To_Step_id = -1;
    SET @To_Flow_Id = -1;
    SET @NextFlowID = -1;
    SET @NextStepID = -1;
    SET @NextFlowRule = -1;
    SET @Jump_Flow_Rule = -1;

    DECLARE @CommandAnd    bit
    DECLARE @Successful    bit

    SET @CommandAnd = 0
    SET @Successful = 0

    SELECT @NextFlowRule = Flow_Rule FROM dbo.UDS_Flow_Step WHERE flow_id = @flow_id and step_id = @step_id;
    
    --进入条件跳转模块
    DECLARE cur_Jump CURSOR FOR
        SELECT  FieldName, Compare, CompareValue, To_Flow_ID, To_Step_ID, Flow_Rule
            FROM dbo.UDS_Flow_Jump
            WHERE Flow_ID = @flow_id and Step_ID = @step_id
            ORDER BY priority;

    OPEN cur_jump;

    --================================================
    FETCH NEXT FROM cur_jump INTO @FieldName, @Compare, @Compare_Value, @To_Flow_Id, @To_Step_id, @Jump_Flow_Rule
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --跳转到自己的为与条件判断开始
        IF @To_Step_id = @Step_id AND @CommandAnd = 0
        BEGIN
            SET @CommandAnd = 1
            SET @Successful = 1 
        END

        IF @CommandAnd = 1
        BEGIN
            --多个与条件判断中
            --第一个不为跳转到自己的为最后一个与条件判断
            IF @To_Step_id <> @Step_id 
            BEGIN
                --与条件判断结束
                SET @CommandAnd = 0
            END

            IF ( 0 = dbo.fn_validate_flow_jump_rule(@DocID, @FieldName, @StaffCaste, @Compare_Value, @Compare) )
                SET @Successful = 0     -- Failed to match the condition of the current jump rule.
            
            --最后一个与条件判断
            IF @CommandAnd = 0
            BEGIN
                --判断都成功
                IF @Successful = 1
                BEGIN
                    SET @NextFlowID = @To_Flow_ID;
                    SET @NextStepID = @To_Step_id
                    BREAK
                END
            END 
        END
        ELSE
        BEGIN
            IF ( 1 = dbo.fn_validate_flow_jump_rule(@DocID, @FieldName, @StaffCaste, @Compare_Value, @Compare) )
            BEGIN
                -- 条件判断成立
                SET @NextFlowID = @To_Flow_ID;
                SET @NextStepID = @To_Step_id
                BREAK
            END
        END
        
        -- goto the next rule
        FETCH NEXT FROM cur_jump INTO @FieldName, @Compare, @Compare_Value, @To_Flow_Id, @To_Step_id, @Jump_Flow_Rule
    END
            
    --================================================
    CLOSE cur_jump
    DEALLOCATE cur_jump

    --一个没找到，进入默认循环
    IF @NextStepID < 0
    BEGIN
        IF EXISTS(SELECT 1 FROM dbo.UDS_Flow_Step WHERE Flow_ID = @flow_id AND Step_ID = @step_id + 1)
        BEGIN
            SET @NextStepID = @step_id + 1
        END
    END
    ELSE
    BEGIN
        -- 使用跳转里指定的流程规则
        SET @NextFlowRule = @Jump_Flow_Rule
    END
    
    -- 如果是跳转到其他子流程的
    IF @NextFlowID > 0
    BEGIN
        SET @NextStepID = 1;
        SELECT @NextFlowID as Flow_ID, @NextStepID AS Step_ID, @NextFlowRule AS Flow_Rule
        RETURN 0;
    END
    
    --对于不是最后步骤的
    SET @NextFlowID = @Flow_ID;  -- Use the current flow
    IF @NextStepID > 0
    BEGIN
        SELECT @NextFlowID as Flow_ID, @NextStepID AS Step_ID, @NextFlowRule AS Flow_Rule
        RETURN 0
    END
    
    --Reaches the end of the flow
    SELECT @NextFlowID as Flow_ID, -1 AS Step_ID, -1 AS Flow_Rule
    RETURN -1
END 
ELSE
BEGIN
    -- The step does not exist.
    SELECT -2 as Flow_ID, -2 AS Step_ID, -2 AS Flow_Rule
    RETURN -2;
END

END
GO